using System.Collections;
using System.Collections.Generic;
using System.Data;
using Mono.Data.Sqlite;
using System.IO;
using Newtonsoft.Json;
using System;
using UnityEngine;


public class DataManager
{
    private const string dbLocation = "Assets\\Data\\gamedata.db";               // Sqlite 数据库文件
    private const string dbDescLocation = "Assets\\Data\\dbDesc.json";           // 数据库表格描述文件
    private const string configDescLocation = "Assets\\Data\\configDesc.json";   // 设置描述文件
    private static SqliteConnection dbConn = new SqliteConnection("Data Source=" + dbLocation);
    public static DataSet dbDesc = JsonConvert.DeserializeObject<DataSet>(File.ReadAllText(dbDescLocation));
    private static DataTable configDesc = JsonConvert.DeserializeObject<DataTable>(File.ReadAllText(configDescLocation));



    /*
     * 本方法应该在这个类被使用前调用，参数用来控制是否重写加载设置。如果数据库中不存在对应的表，那么它会被自动创建。
     */
    public static void Init(bool reloadConfigs = false)
    {
        dbConn.Open();

        // create missing tables
        List<string> existed = new List<string>();
        var command = dbConn.CreateCommand();
        command.CommandText = "SELECT name FROM Sqlite_master where type='table'";
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            string tableName = reader.GetString(0);
            if (!(configDesc.Select(String.Format("name = '{0}'", tableName)).Length > 0) || !reloadConfigs)
            {
                Debug.Log("skip " + tableName);
                existed.Add(tableName);
            }
        }
        CreateTables(existed);

        // load configs to tables if needed
        LoadConfigs(existed);
    }

    /*
     * 本方法应该在这个类被结束后调用
     */
    public static void End()
    {
        dbConn.Close();
    }

    /*
     * 这个方法用于从数据库中查询结果，每次可以查询多列保存在内层中，外层代表了满足筛选条件的行数
     */
    public static Dictionary<string, List<dynamic>> GetAttrs(DatabaseQuery query)
    {
        if (query.GetMode().StartsWith("select"))
        {
            var command = dbConn.CreateCommand();
            command.CommandText = query.GetCMD();
            var reader = command.ExecuteReader();

            // init result
            string[] attrs = query.GetTargetAttrs();
            Dictionary<string, List<dynamic>> result = new Dictionary<string, List<dynamic>>();
            foreach (string attr in attrs)
            {
                result.Add(attr, new List<dynamic>());
            }

            // read data
            DataTable tableDesc = dbDesc.Tables[query.GetTargetTableName()];
            while (reader.Read())
            {
                for (int i = 0; i < attrs.Length; i++)
                {
                    string attr = attrs[i];
                    switch (tableDesc.Select("name='" + attr + "'")[0]["type"].ToString())
                    {
                        case "INTEGER":
                            result[attr].Add(reader.GetInt32(i));
                            break;
                        case "REAL":
                            result[attr].Add(reader.GetFloat(i));
                            break;
                        case "TEXT":
                            result[attr].Add(reader.GetString(i));
                            break;
                        default:
                            break;
                    }
                }
            }

            return result;
        }
        else
        {
            throw new System.Exception("non-select query are passed to GetAttrs");
        }
    }

    public static void UpdateDB(DatabaseQuery query)
    {
        if (query.GetMode().StartsWith("insert") || query.GetMode().StartsWith("update"))
        {
            var command = dbConn.CreateCommand();
            command.CommandText = query.GetCMD();
            command.ExecuteNonQuery();
        }
        else
        {
            throw new System.Exception("non-insert|update query are passed to GetAttrs");
        }
    }


    private static void CreateTables(List<string> skip)
    {
        foreach (DataTable table in dbDesc.Tables)
        {
            if (!skip.Contains(table.TableName))
            {
                // try drop first
                try
                {
                    DatabaseQuery query = new DatabaseQuery().DropTable(table.TableName);
                    System.Console.WriteLine(query.GetCMD());
                    var command = dbConn.CreateCommand();
                    command.CommandText = query.GetCMD();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    // 尝试 drop 表失败后直接无视 exception
                }
                // later create
                {
                    DatabaseQuery query = new DatabaseQuery().CreateTable(table);
                    System.Console.WriteLine(query.GetCMD());
                    var command = dbConn.CreateCommand();
                    command.CommandText = query.GetCMD();
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    private static void LoadConfigs(List<string> skip)
    {
        List<DataTable> configs = new List<DataTable>();
        foreach (DataRow config in configDesc.Rows)
        {
            string tableName = config["name"].ToString();
            if (!skip.Contains(tableName))
            {
                switch (config["type"].ToString())
                {
                    case "csv":
                        Debug.Log("loading config: " + config["name"].ToString());
                        CSVConfigAsset asset = new CSVConfigAsset(config["path"].ToString());
                        DataTable dt = asset.GetDataTable();
                        dt.TableName = tableName;
                        configs.Add(dt);
                        break;
                    default:
                        break;
                }
            }
        }
        foreach (DataTable dt in configs)
        {
            SaveDataTable(dt);
        }
    }


    private static void SaveDataTable(DataTable dt)
    {
        var cmd = dbConn.CreateCommand();
        cmd.CommandText = string.Format("SELECT * FROM {0}", dt.TableName);
        var adapter = new SqliteDataAdapter(cmd);
        SqliteCommandBuilder builder = new SqliteCommandBuilder(adapter);
        adapter.Update(dt);
    }
}